using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;
using eShop.Utility;
using eShop.Model;


namespace eShop.DataAccess
{
    /// <summary>
    /// This object represents the Private Fields of TblProductStock.
    /// </summary>

    [System.ComponentModel.DataObject]
    public class ProductStockDAO : DatabaseUtility
    {
        #region TblProductStock Static Methods
        /************************************************************************************
                         -- DELETE METHODS --
        *************************************************************************************/
        /************************* Architecure note:**********************************
        We have used the employeeId parameter as SqlInt32, to bind the delete method with object data source and view control directly.
        ****************************************************************************/
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
        public static bool DeleteTblProductStock
        (
            Int32 stockId
        )
        {
            //Validate Input
            if (stockId <= GetStockIdMinValue)
                throw (new ArgumentOutOfRangeException("stockId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@StockId", SqlDbType.Int, 0, ParameterDirection.Input, stockId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTSTOCK_DELETETBLPRODUCTSTOCK);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);

        }

        /// <summary>
        /// Deletes a set of employees from the database.
        /// </summary>
        public static bool DeleteTblProductStockList(System.Collections.Generic.List<Int32> stockIdListToDelete)
        {
            //Validate Input
            foreach (Int32 stockId in stockIdListToDelete)
                if (stockId <= GetStockIdMinValue)
                    throw (new ArgumentOutOfRangeException("stockId"));

            //performing deletion operation //

            string xmlString = FormatXMLForIdArray(stockIdListToDelete);

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@StockIdList", SqlDbType.Xml, xmlString.ToString().Length, ParameterDirection.Input, xmlString.ToString());
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTSTOCK_DELETETBLPRODUCTSTOCKLIST);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == stockIdListToDelete.Count ? true : false);
        }

        /************************************************************************************
                                 -- GET BY PRIMARY METHODS --
        *************************************************************************************/
        /************************* Architecure note:**********************************
        Retrives an Employee from database, based on it's primary key.
        To view or update an employee, everytime we must have to use this type of 'get' method, 
        since we don't have direct access to the Primary key value for assigning value.
        We have used the employeeId parameter as SqlInt32, to bind the delete method with object data source and view control directly.
        /*****************************************************************************/
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static ProductStock GetTblProductStockByStockId(Int32 stockId)
        {
            if (stockId <= GetStockIdMinValue)
                throw (new ArgumentOutOfRangeException("stockId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@StockId", SqlDbType.Int, 0, ParameterDirection.Input, stockId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTSTOCK_GETTBLPRODUCTSTOCKBYSTOCKID);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductStockCollectionFromReader);
            List<ProductStock> objCollection = ((List<ProductStock>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        /************************************************************************************
                         -- GET BY FOREIGN KEY METHODS --
        *************************************************************************************/

        /************************************************************************************
                 -- GET ALL METHODS --
        *************************************************************************************/
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static List<ProductStock> GetAllTblProductStock()
        {

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTSTOCK_GETALLTBLPRODUCTSTOCK);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductStockCollectionFromReader);
            List<ProductStock> objCollection = ((List<ProductStock>)ExecuteReaderCmd(sqlCmd, test));

            return objCollection;
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static List<ProductStock> GetAllTblProductStockPaged(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (orderBy == "")
                orderBy = "StockId";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTSTOCK_GETALLTBLPRODUCTSTOCKPAGED);

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductStockCollectionFromReader);
            List<ProductStock> objCollection = ((List<ProductStock>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static int GetAllTblProductStockPagedCount(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTSTOCK_GETALLTBLPRODUCTSTOCKPAGEDCOUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        #endregion
        #region Employee Instance Methods
        /************************************************************************************
                                    -- SAVE (INSERT/UPDATE) METHODS (INSTANCE) --
        *************************************************************************************/
        /// <summary>
        /// Saves (inserts and updates) an Employee into database.
        /// The insertion occur if the EmployeeId conatins default value.
        /// Otherwises it updates the Employee into the database, based on the primary key value stored in EmployeeId.
        /// This technique ensures an Employee will not be updated without having a valid EmployeeId.
        /// </summary>
        public static bool Save(ProductStock productStock)
        {
            if (productStock.StockId <= GetStockIdMinValue)
            {
                Int32 TempId = CreateNewTblProductStock(productStock);
                if (TempId > 0)
                {
                    productStock.StockId = TempId;
                    return true;
                }
                else
                {
                    // error occurs
                    return false;
                }
            }
            else
            {	//This is a update Command
                return (UpdateTblProductStock(productStock));
            }

        }

        public static Int32 CreateNewTblProductStock(ProductStock newTblProductStock)
        {
            // validate Parameters
            if (newTblProductStock == null)
                throw (new ArgumentNullException("newTblProductStock"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@Size", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductStock.Description);
            AddParameterToSqlCmd(sqlCmd, "@Weight", SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProductStock.Weight);
            AddParameterToSqlCmd(sqlCmd, "@WeightUnit", SqlDbType.NVarChar, 50, ParameterDirection.Input, newTblProductStock.WeightUnit);
            AddParameterToSqlCmd(sqlCmd, "@WidthWholeNumberFirstDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@WidthWholeNumberSecondDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@WidthFractionNumberFirstDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@WidthFractionNumberSecondDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@WidthValue", SqlDbType.Decimal, 9, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@WidthUnit", SqlDbType.NVarChar, 50, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@DiameterWholeNumberFirstDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@DiameterWholeNumberSecondDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@DiameterFractionNumberFirstDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@DiameterFractionNumberSecondDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@DiameterValue", SqlDbType.Decimal, 9, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@DiameterUnit", SqlDbType.NVarChar, 50, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@ThreadCountWholeNumberFirstDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@ThreadCountWholeNumberSecondDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@ThreadCountFractionNumberFirstDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@ThreadCountFractionNumberSecondDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@ThreadCountValue", SqlDbType.Decimal, 9, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@ThreadCountUnit", SqlDbType.NVarChar, 50, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@LengthWholeNumberFirstDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@LengthWholeNumberSecondDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@LengthFractionNumberFirstDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@LengthFractionNumberSecondDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@LengthValue", SqlDbType.Decimal, 9, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@LengthUnit", SqlDbType.NVarChar, 50, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@StockAmount", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductStock.StockAvailable);
            AddParameterToSqlCmd(sqlCmd, "@ProductId", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductStock.ProductId);
            AddParameterToSqlCmd(sqlCmd, "@PkgQuanity", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductStock.StockAlertCount);
            AddParameterToSqlCmd(sqlCmd, "@UseDecimalForSize", SqlDbType.Bit, 0, ParameterDirection.Input, false);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTSTOCK_CREATENEWTBLPRODUCTSTOCK);
            ExecuteScalarCmd(sqlCmd);
            return (Int32.Parse(sqlCmd.Parameters["@ReturnValue"].Value.ToString()));

        }
        /// <summary>
        /// Updates an TblProductStock instance to the database.
        /// </summary>
        public static bool UpdateTblProductStock(ProductStock newTblProductStock)
        {
            // validate Parameters
            if (newTblProductStock == null)
                throw (new ArgumentNullException("newTblProductStock"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@StockId", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductStock.StockId);
            AddParameterToSqlCmd(sqlCmd, "@Size", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductStock.Description);
            AddParameterToSqlCmd(sqlCmd, "@Weight", SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProductStock.Weight);
            AddParameterToSqlCmd(sqlCmd, "@WeightUnit", SqlDbType.NVarChar, 50, ParameterDirection.Input, newTblProductStock.WeightUnit);
            AddParameterToSqlCmd(sqlCmd, "@WidthWholeNumberFirstDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@WidthWholeNumberSecondDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@WidthFractionNumberFirstDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@WidthFractionNumberSecondDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@WidthValue", SqlDbType.Decimal, 9, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@WidthUnit", SqlDbType.NVarChar, 50, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@DiameterWholeNumberFirstDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@DiameterWholeNumberSecondDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@DiameterFractionNumberFirstDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@DiameterFractionNumberSecondDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@DiameterValue", SqlDbType.Decimal, 9, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@DiameterUnit", SqlDbType.NVarChar, 50, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@ThreadCountWholeNumberFirstDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@ThreadCountWholeNumberSecondDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@ThreadCountFractionNumberFirstDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@ThreadCountFractionNumberSecondDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@ThreadCountValue", SqlDbType.Decimal, 9, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@ThreadCountUnit", SqlDbType.NVarChar, 50, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@LengthWholeNumberFirstDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@LengthWholeNumberSecondDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@LengthFractionNumberFirstDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@LengthFractionNumberSecondDigit", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@LengthValue", SqlDbType.Decimal, 9, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@LengthUnit", SqlDbType.NVarChar, 50, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@StockAmount", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductStock.StockAvailable);
            AddParameterToSqlCmd(sqlCmd, "@ProductId", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductStock.ProductId);
            AddParameterToSqlCmd(sqlCmd, "@PkgQuanity", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductStock.StockAlertCount);
            AddParameterToSqlCmd(sqlCmd, "@UseDecimalForSize", SqlDbType.Bit, 0, ParameterDirection.Input, false);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTSTOCK_UPDATETBLPRODUCTSTOCK);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);
        }


        public static List<ProductStock> GetProductStockByProductId(int productId)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ProductId", SqlDbType.Int, 0, ParameterDirection.Input, productId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTSTOCK_GET_PRODUCT_STOCK_BY_PRODUCTID);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductStockCollectionFromReader);
            List<ProductStock> objCollection = ((List<ProductStock>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static List<ProductStock> GetStockOutItems(int stockLevel)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@StockLevel", SqlDbType.Int, 0, ParameterDirection.Input, stockLevel);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTSTOCK_GET_PRODUCT_STOCK_BY_STOCK_LEVEL);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductStockCollectionFromReader);
            List<ProductStock> objCollection = ((List<ProductStock>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }


        public static List<ProductStock> GetStockOutItemsPaged(int stockLevel, string orderBy, int startRow, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@StockLevel", SqlDbType.Int, 0, ParameterDirection.Input, stockLevel);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTSTOCK_GET_PRODUCT_STOCK_BY_STOCK_LEVELPAGED);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductStockCollectionFromReader);
            List<ProductStock> objCollection = ((List<ProductStock>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }


        public static int GetStockOutItemsCount(int stockLevel)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTSTOCK_GET_PRODUCT_STOCK_BY_STOCK_LEVELPAGEDCOUNT);
            AddParameterToSqlCmd(sqlCmd, "@StockLevel", SqlDbType.Int, 0, ParameterDirection.Input, stockLevel);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        #endregion

        #region TblProductStock Related Collections
        /// <summary>
        /// Creates and returns a strongly typed collection of Employee custom entity. 
        /// The colelction is created through iterating on the IdataReader object which contains Employee information, as a set of records, similar to tabular format.
        /// </summary>
        public static IList GenerateTblProductStockCollectionFromReader(IDataReader returnData)
        {

            //creating the instance of Employee collection
            List<ProductStock> colTblProductStock = new List<ProductStock>();

            /************************* Architecure note:**********************************
             * Below code includes the null value functionality to retieve the data which has nill value in database end.
             * Design consideration:
             * Besides general data fields, special care should be taken for primary keys, to assign '0'/defaultvalue, rather passing 'Null' value in constructor parameter.
             * Although we are considering sqldb tyoe data for the current data container, but while retireving data from database end, through datareader object, we need to cast data reader objects using .net primitive data type, 
             * rather using sqldb type to cast, since data reader objects don't support sql db type to be casted.
             *****************************************************************************/

            //Iterating thriugh the data reader, to generate Employee collection.

            //getting the Employee collection by iterating thru the data reader
            while (returnData.Read())
            {
                //passing the TblProductStock constructor parameters from the current instance of data reader fields.
                ProductStock newTblProductStock = new ProductStock
                (
                    returnData["StockId"] == System.DBNull.Value ? GetStockIdMinValue : (Int32)returnData["StockId"],
                    returnData["ProductId"] == System.DBNull.Value ? 0 : (Int32)returnData["ProductId"],
                    returnData["Size"] == System.DBNull.Value ? string.Empty : (String)returnData["Size"],
                    returnData["Weight"] == System.DBNull.Value ? 0 : (Double)((Decimal)returnData["Weight"]),
                    returnData["WeightUnit"] == System.DBNull.Value ? string.Empty : (String)returnData["WeightUnit"],
                    returnData["StockAmount"] == System.DBNull.Value ? 0 : (Int32)returnData["StockAmount"],
                    returnData["PkgQuanity"] == System.DBNull.Value ? 0 : (Int32)returnData["PkgQuanity"]
                );
                //adding the Employee to the collection
                colTblProductStock.Add(newTblProductStock);
            }

            //returns the collection of Companys 
            return (colTblProductStock);
        }

        #endregion
        #region Constants and Default Values

        public static Int32 GetStockIdMinValue
        {
            get
            {
                return 0;
            }
        }

        //create method related contants
        internal const string SP_TBLPRODUCTSTOCK_CREATENEWTBLPRODUCTSTOCK = "sp_TblProductStock_CreateNewTblProductStock";
        internal const string SP_TBLPRODUCTSTOCK_UPDATETBLPRODUCTSTOCK = "sp_TblProductStock_UpdateTblProductStock";
        internal const string SP_TBLPRODUCTSTOCK_DELETETBLPRODUCTSTOCK = "sp_TblProductStock_DeleteTblProductStock";
        internal const string SP_TBLPRODUCTSTOCK_DELETETBLPRODUCTSTOCKLIST = "sp_TblProductStock_DeleteTblProductStockList";
        internal const string SP_TBLPRODUCTSTOCK_GETTBLPRODUCTSTOCKBYSTOCKID = "sp_TblProductStock_GetTblProductStockByStockId";
        internal const string SP_TBLPRODUCTSTOCK_GETALLTBLPRODUCTSTOCK = "sp_TblProductStock_GetAllTblProductStock";
        internal const string SP_TBLPRODUCTSTOCK_GETALLTBLPRODUCTSTOCKPAGED = "sp_TblProductStock_GetAllTblProductStockPaged";
        internal const string SP_TBLPRODUCTSTOCK_GETALLTBLPRODUCTSTOCKPAGEDCOUNT = "sp_TblProductStock_GetAllTblProductStockPagedCount";
        private const string SP_TBL_PRODUCTSTOCK_GET_PRODUCT_STOCK_BY_PRODUCTID = "sp_TblProductStock_Get_ProductStock_By_ProductId";
        private const string SP_TBL_PRODUCTSTOCK_GET_PRODUCT_STOCK_BY_STOCK_LEVEL = "sp_TblProductStock_Get_ProductStock_By_Stock_Level";
        private const string SP_TBL_PRODUCTSTOCK_GET_PRODUCT_STOCK_BY_STOCK_LEVELPAGED = "sp_TblProductStock_Get_ProductStock_By_Stock_LevelPaged";
        private const string SP_TBL_PRODUCTSTOCK_GET_PRODUCT_STOCK_BY_STOCK_LEVELPAGEDCOUNT = "sp_TblProductStock_Get_ProductStock_By_Stock_LevelPagedCount";

        #endregion
    }
}
